﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace VietKinsNutrition.Data
{
    public class ClsTblLophocDAL
    {
        ClsConnection ClsCnn = new ClsConnection();
        public void Insert(string Tenlop, int PhonghocId, int NamhocId, int DotuoiId, int PhanloailopId, bool Bantru, bool Doclap, int CtgdId)
        {
            string NamePrc = "sproc_TblLop_Add";
            SqlCommand Cmm = new SqlCommand();
            Cmm.Parameters.Add(new SqlParameter("@Tenlop", Tenlop));
            Cmm.Parameters.Add(new SqlParameter("@PhonghocId", PhonghocId));
            Cmm.Parameters.Add(new SqlParameter("@DotuoiId", DotuoiId));
            Cmm.Parameters.Add(new SqlParameter("@NamhocId", NamhocId));
            Cmm.Parameters.Add(new SqlParameter("@PhanloailopId", PhanloailopId));
            Cmm.Parameters.Add(new SqlParameter("@Bantru", Bantru));
            Cmm.Parameters.Add(new SqlParameter("@Doclap", Doclap));
            Cmm.Parameters.Add(new SqlParameter("@ChuongtrinhGDId", CtgdId));
            ClsConnection.SqlDAdpter(NamePrc, Cmm);
        }
         
        public DataSet TblLopByDotuoiId(int DotuoiId)
        {
            string NamePrc = "TblLopByDotuoiId";
            string NameTable = "TblLop";
            DataSet Dset = new DataSet();

            SqlCommand Cmm = new SqlCommand();
            Cmm.Parameters.Add(new SqlParameter("@DotuoiId", DotuoiId));

            SqlDataAdapter Dap;
            Cmm.Connection = ClsConnection.SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new SqlDataAdapter(Cmm);
            Dap.Fill(Dset);
            return Dset;
        }
        public DataSet DatasetByPhanloailopId(int PhanloailopId)
        {
            string NamePrc = "sproc_TblLop_GetByPhanloailopId";
            string NameTable = "TblLop";
            DataSet Dset = new DataSet();

            SqlCommand Cmm = new SqlCommand();
            Cmm.Parameters.Add(new SqlParameter("@PhanloailopId", PhanloailopId));

            SqlDataAdapter Dap;
            Cmm.Connection = ClsConnection.SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new SqlDataAdapter(Cmm);
            Dap.Fill(Dset);
            return Dset;
        }
        public void Edit(int Id, string Tenlop, int PhonghocId, int NamhocId, int DotuoiId, int PhanloailopId, bool Bantru, bool Doclap, int ctgdId)
        {
            string NamePrc = "sproc_TblLop_Update";
            SqlCommand Cmm = new SqlCommand();
            Cmm.Parameters.Add(new SqlParameter("@Tenlop", Tenlop));
            Cmm.Parameters.Add(new SqlParameter("@Id", Id));
            Cmm.Parameters.Add(new SqlParameter("@PhonghocId", PhonghocId));
            Cmm.Parameters.Add(new SqlParameter("@DotuoiId", DotuoiId));
            Cmm.Parameters.Add(new SqlParameter("@NamhocId", NamhocId));
            Cmm.Parameters.Add(new SqlParameter("@PhanloailopId", PhanloailopId));
            Cmm.Parameters.Add(new SqlParameter("@Bantru", Bantru));
            Cmm.Parameters.Add(new SqlParameter("@Doclap", Doclap));
            Cmm.Parameters.Add(new SqlParameter("@ChuongtrinhGdId", ctgdId));
            ClsConnection.SqlDAdpter(NamePrc, Cmm);
        }

        //
        public int ReturnLophocIdbyNamhocIdPhonghocIdDotuoiIdPhanloailopIdName(int NamhocId, int PhonghocId, int DotuoiId, int PhanloailopId, string Tenlop)
        {
            int iReturn = -1;
            string NamePrc = "sproc_TblLop_GetByPhonghocId_namhocId_dotuoiId_phanloailopId_name";
            DataSet Dset = new DataSet();

            SqlCommand Cmm = new SqlCommand();
            Cmm.Parameters.Add(new SqlParameter("@NamhocId", NamhocId));
            Cmm.Parameters.Add(new SqlParameter("@PhonghocId", PhonghocId));
            Cmm.Parameters.Add(new SqlParameter("@DotuoiId", DotuoiId));
            Cmm.Parameters.Add(new SqlParameter("@PhanloailopId", PhanloailopId));
            Cmm.Parameters.Add(new SqlParameter("@Tenlop", Tenlop));

            SqlDataAdapter Dap;
            Cmm.Connection = ClsConnection.SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new SqlDataAdapter(Cmm);
            Dap.Fill(Dset);
            if (Dset.Tables[0].Rows.Count > 0)
            {
                iReturn = int.Parse(Dset.Tables[0].Rows[0]["LophocId"].ToString());
            }
            return iReturn;
        }

        public DataSet DatasetGetAllByNamhocId(int NamhocId)
        {
            string NamePrc = "sproc_TblLop_GetAll";
            string NameTable = "TblLop";
            DataSet Dset = new DataSet();

            SqlCommand Cmm = new SqlCommand();
            Cmm.Parameters.Add(new SqlParameter("@NamhocId", NamhocId));

            SqlDataAdapter Dap;
            Cmm.Connection = ClsConnection.SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new SqlDataAdapter(Cmm);
            Dap.Fill(Dset);
            return Dset;
        }

        public DataTable GetAll(int NamhocId)
        {
            string NameTable = "TblLop";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            string NamePrc = "sproc_TblLop_GetAll";
            NamePrc = QueryString.SP_LOP_GETALL.Replace("@NamhocId", NamhocId.ToString());
            ClsConnection.ExecuteNonQuery(NamePrc, Dset, NameTable, NamhocId);
            return Dset.Tables[NameTable];
        }
        public static DataTable GetByNamhoc(int NamhocId)
        {
            object[] value = { NamhocId };
            return DataAccess.FillTable(ClsConnection.connectString, "sproc_TblLop_GetAll", value, true);
        }
        //
        public DataSet ReturDset_SelectAll(int NamhocId)
        {
            string NamePrc = "sproc_TblLop_SelectAll";
            string NameTable = "TblLop";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            SqlCommand Cmm = new SqlCommand();
            Cmm.Parameters.Add(new SqlParameter("@NamhocId", NamhocId));

            SqlDataAdapter Dap;
            Cmm.Connection = ClsConnection.SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new SqlDataAdapter(Cmm);

            Dap.Fill(Dset.Tables[NameTable]);

            BindingSource Binding = new BindingSource(Dset, NameTable);

            return Dset;

        }
        public BindingSource SelectAll(int PhonghocId, int PhanloailopId, int DotuoiId, int NamhocId)
        {
            string NamePrc = "sproc_TblLop_Get";
            string NameTable = "TblLop";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            SqlCommand Cmm = new SqlCommand();
            Cmm.Parameters.Add(new SqlParameter("@PhonghocId", PhonghocId));
            Cmm.Parameters.Add(new SqlParameter("@PhanloailopId", PhanloailopId));
            Cmm.Parameters.Add(new SqlParameter("@DotuoiId", DotuoiId));
            Cmm.Parameters.Add(new SqlParameter("@NamhocId", NamhocId));

            SqlDataAdapter Dap;
            Cmm.Connection = ClsConnection.SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new SqlDataAdapter(Cmm);

            Dap.Fill(Dset.Tables[NameTable]);

            BindingSource Binding = new BindingSource(Dset, NameTable);

            return Binding;

        }

        public void Delete(int Id)
        {
            string NamePrc = "sproc_TblLop_Delete";
            SqlCommand Cmm = new SqlCommand();
            Cmm.Parameters.Add(new SqlParameter("@Id", Id));
            ClsConnection.SqlDAdpter(NamePrc, Cmm);
        }

        public void DeleteByNamhocId(int NamhocId)
        {
            string NamePrc = "sproc_TblLop_DeleteByNamhocId";
            SqlCommand Cmm = new SqlCommand();
            Cmm.Parameters.Add(new SqlParameter("@NamhocId", NamhocId));
            ClsConnection.SqlDAdpter(NamePrc, Cmm);
        }

        public DataSet SelectByPhonghocId(int PhonghocId)
        {
            string NamePrc = "sproc_TblLop_GetByPhonghocId";
            string NameTable = "TblLop";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            SqlCommand Cmm = new SqlCommand();
            SqlParameter NamhocId_Paramet = new SqlParameter();
            NamhocId_Paramet.ParameterName = "@PhonghocId";
            NamhocId_Paramet.SqlDbType = System.Data.SqlDbType.Int;
            NamhocId_Paramet.Value = PhonghocId;
            Cmm.Parameters.Add(NamhocId_Paramet);
            SqlDataAdapter Dap;
            Cmm.Connection = ClsConnection.SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new SqlDataAdapter(Cmm);

            Dap.Fill(Dset.Tables[NameTable]);

            return Dset;
        }
        public DataSet SelectById(int Id)
        {
            string NamePrc = "sproc_TblLop_GetById";
            string NameTable = "TblLop";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);
            SqlCommand Cmm = new SqlCommand();
            SqlParameter NamhocId_Paramet = new SqlParameter();
            NamhocId_Paramet.ParameterName = "@Id";
            NamhocId_Paramet.SqlDbType = System.Data.SqlDbType.Int;
            NamhocId_Paramet.Value = Id;
            Cmm.Parameters.Add(NamhocId_Paramet);
            SqlDataAdapter Dap;
            Cmm.Connection = ClsConnection.SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new SqlDataAdapter(Cmm);

            Dap.Fill(Dset.Tables[NameTable]);

            return Dset;
        }

        public DataTable GetTable(int Id)
        {
            SqlConnection cn = ClsConnection.SQLConnect();
            DataTable _table = new DataTable();
            SqlCommand comand = new SqlCommand("PrcTblDm_Phanloailop", cn);
            comand.CommandType = CommandType.StoredProcedure;
            try
            {
                comand.Parameters.Add(new SqlParameter("@id", Id));
                comand.ExecuteNonQuery();
                SqlDataAdapter Adapter = new SqlDataAdapter(comand);
                Adapter.Fill(_table);
            }
            catch (System.Exception ex)
            { MessageBox.Show(" Lỗi " + ex.Message); }
            return _table;
        }
    }
}
